/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package workforce.limousine.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Locale;
import javax.sql.DataSource;
import limousine.vo.BookingInfo;
import org.apache.log4j.Logger;

/**
 *
 * @author krisada
 */
public class BookingSearchImpl implements BookingSearcher {

    private DataSource dataSource = null;
    private int limit = 20;
    private Logger log = Logger.getLogger(this.getClass());

    public BookingSearchImpl() {
    }

    public void setLimit(int limit) {
        this.limit = limit;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public List<BookingInfo> query(int offset) {
        List<BookingInfo> list = new ArrayList<BookingInfo>();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT A.BOOKING_ID AS BOOKING_ID,");
        sql.append("IFNULL(A.UPDATE_DATE, CURRENT_TIMESTAMP) AS BOOKING_TIME,");
        sql.append("IFNULL(B.GUEST, '-') AS GUEST_NAME,");
        sql.append("CONCAT(CONCAT(B.FROM_AREA, '-'), B.TO_AREA) AS TO_AREA,");
        sql.append("IFNULL(C.CUSTOMER_NAME, '-') AS CUSTOMER_NAME ");
        sql.append("FROM booking A, booking_detail B, customer C ");
        sql.append("WHERE A.BOOKING_ID = B.BOOKING_ID");
        sql.append(" AND A.CUSTOMER_ID = C.CUSTOMER_ID");
        sql.append(" AND EXTRACT(DAY FROM A.CREATE_DATE) = EXTRACT(DAY FROM NOW())");
        sql.append(" AND EXTRACT(MONTH FROM A.CREATE_DATE) = EXTRACT(MONTH FROM NOW())");
        sql.append(" AND EXTRACT(YEAR FROM A.CREATE_DATE) = EXTRACT(YEAR FROM NOW())");
        sql.append(" ORDER BY A.UPDATE_DATE DESC");
        sql.append(" LIMIT ").append(limit);
        sql.append(" OFFSET ").append(offset);
        log.info("SQL:[" + sql.toString() + "]");
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            int r = offset + 1;
            while (rs.next()) {
                BookingInfo b = new BookingInfo();
                b.setRow(r);
                b.setId(rs.getString(1));
                java.util.Date time = new java.util.Date(rs.getTimestamp(2).getTime());
                b.setTime(sf.format(time));
                b.setGuest(rs.getString(3));
                b.setArea(rs.getString(4));
                b.setCustomerName(rs.getString(5));
                list.add(b);
                r++;
            }
            rs.close();
            stmt.close();
            rs = null;
            stmt = null;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }

        return list;
    }

    public List<BookingInfo> search(int offset, Object[] find) {
        List<BookingInfo> list = new ArrayList<BookingInfo>();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT A.BOOKING_ID AS BOOKING_ID,");
        sql.append("IFNULL(A.UPDATE_DATE,CURRENT_TIMESTAMP) AS BOOKING_TIME,");
        sql.append("IFNULL(B.GUEST,'-') AS GUEST_NAME,");
        sql.append("CONCAT(CONCAT(B.FROM_AREA, '-'), B.TO_AREA) AS TO_AREA, ");
        sql.append("IFNULL(C.CUSTOMER_NAME,'-') AS CUSTOMER_NAME ");
        sql.append("FROM booking A, booking_detail B, customer C ");
        sql.append("WHERE A.BOOKING_ID = B.BOOKING_ID");
        sql.append(" AND A.CUSTOMER_ID = C.CUSTOMER_ID");
        sql.append(" AND EXTRACT(DAY FROM A.CREATE_DATE) = EXTRACT(DAY FROM NOW())");
        sql.append(" AND EXTRACT(MONTH FROM A.CREATE_DATE) = EXTRACT(MONTH FROM NOW())");
        sql.append(" AND EXTRACT(YEAR FROM A.CREATE_DATE) = EXTRACT(YEAR FROM NOW())");
        sql.append(" ORDER BY A.UPDATE_DATE DESC ");
        sql.append(" LIMIT ").append(limit);
        sql.append(" OFFSET ").append(offset);
        log.info("SQL:[" + sql.toString() + "]");
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            int r = offset + 1;
            while (rs.next()) {
                BookingInfo b = new BookingInfo();
                b.setRow(r);
                b.setId(rs.getString(1));
                java.util.Date time = new java.util.Date(rs.getTimestamp(2).getTime());
                b.setTime(sf.format(time));
                b.setGuest(rs.getString(3));
                b.setArea(rs.getString(4));
                b.setCustomerName(rs.getString(5));
                list.add(b);
                r++;
            }
            rs.close();
            stmt.close();
            sf = null;
            rs = null;
            stmt = null;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return list;
    }

    public List<BookingInfo> findByDate(int offset, String find) {
        SimpleDateFormat sf1 = new SimpleDateFormat("yyyy-MM-dd");
        Calendar cd = Calendar.getInstance(Locale.US);
        try {
            java.util.Date df = sf1.parse(find);
            cd.setTime(df);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        List<BookingInfo> list = new ArrayList<BookingInfo>();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT A.BOOKING_ID AS BOOKING_ID,");
        sql.append("IFNULL(A.UPDATE_DATE,CURRENT_TIMESTAMP) AS BOOKING_TIME,");
        sql.append("IFNULL(B.GUEST,'-') AS GUEST_NAME,");
        sql.append("CONCAT(CONCAT(B.FROM_AREA, '-'), B.TO_AREA) AS TO_AREA, C.CUSTOMER_NAME AS CUSTOMER_NAME ");
        sql.append("FROM booking A, booking_detail B, customer C ");
        sql.append("WHERE A.BOOKING_ID = B.BOOKING_ID");
        sql.append(" AND A.CUSTOMER_ID = C.CUSTOMER_ID");
        sql.append(" AND A.CREATE_DATE BETWEEN ");
        sql.append("'").append(sf1.format(cd.getTime())).append("' AND '");
        cd.set(Calendar.DAY_OF_MONTH, cd.get(Calendar.DAY_OF_MONTH) + 1);
        sql.append(sf1.format(cd.getTime())).append("'");
        sql.append(" ORDER BY A.UPDATE_DATE DESC ");
        sql.append("LIMIT ").append(limit);
        sql.append(" OFFSET ").append(offset);
        log.info("SQL:[" + sql.toString() + "]");
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            int r = offset + 1;
            while (rs.next()) {
                BookingInfo b = new BookingInfo();
                b.setRow(r);
                b.setId(rs.getString(1));
                java.util.Date time = new java.util.Date(rs.getTimestamp(2).getTime());
                b.setTime(sf.format(time));
                b.setGuest(rs.getString(3));
                b.setArea(rs.getString(4));
                b.setCustomerName(rs.getString(5));
                list.add(b);
                r++;
            }
            rs.close();
            stmt.close();
            sf1 = null;
            sf = null;
            rs = null;
            stmt = null;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return list;
    }

    public List<BookingInfo> findByCustomer(int offset, String customerId, String find) {
        SimpleDateFormat sf1 = new SimpleDateFormat("yyyy-MM-dd");
        Calendar cd = Calendar.getInstance(Locale.US);
        try {
            java.util.Date df = sf1.parse(find);
            cd.setTime(df);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        List<BookingInfo> list = new ArrayList<BookingInfo>();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT A.BOOKING_ID AS BOOKING_ID,");
        sql.append("IFNULL(A.UPDATE_DATE, CURRENT_TIMESTAMP) AS BOOKING_TIME,");
        sql.append("IFNULL(B.GUEST,'-') AS GUEST_NAME,");
        sql.append("CONCAT(CONCAT(B.FROM_AREA, '-'), B.TO_AREA) AS TO_AREA, C.CUSTOMER_NAME AS CUSTOMER_NAME ");
        sql.append("FROM booking A, booking_detail B, customer C ");
        sql.append("WHERE A.BOOKING_ID = B.BOOKING_ID");
        sql.append(" AND A.CUSTOMER_ID = C.CUSTOMER_ID");
        sql.append(" AND A.CUSTOMER_ID = '").append(customerId).append("'");
        sql.append(" AND A.CREATE_DATE BETWEEN ");
        sql.append("'").append(sf1.format(cd.getTime())).append("' AND '");
        cd.set(Calendar.DAY_OF_MONTH, cd.get(Calendar.DAY_OF_MONTH) + 1);
        sql.append(sf1.format(cd.getTime())).append("'");
        sql.append(" ORDER BY A.UPDATE_DATE DESC ");
        sql.append("LIMIT ").append(limit);
        sql.append(" OFFSET ").append(offset);
        log.info("SQL:[" + sql.toString() + "]");
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            int r = offset + 1;
            while (rs.next()) {
                BookingInfo b = new BookingInfo();
                b.setRow(r);
                b.setId(rs.getString(1));
                java.util.Date time = new java.util.Date(rs.getTimestamp(2).getTime());
                b.setTime(sf.format(time));
                b.setGuest(rs.getString(3));
                b.setArea(rs.getString(4));
                b.setCustomerName(rs.getString(5));
                list.add(b);
                r++;
            }
            rs.close();
            stmt.close();
            sf1 = null;
            sf = null;
            rs = null;
            stmt = null;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return list;
    }
}
